from django.shortcuts import render
from django.http import JsonResponse
import pymysql
connection = pymysql.connect(host='127.0.0.1', user='root', password='777271ll',db='schooldata')

#在这儿写你的函数实现，在url里用api2.xxxxx调用
def admin_student(request):
    student_id=request.GET['student_id']
    sname=request.GET['sname']
    print(sname,student_id)
    cur=connection.cursor()
    sql=f'''
    SELECT s.sname, s.student_id, s.grade, s.sex, s.native_place, s.mobile_phone, d.dept_name FROM student s
    JOIN department d ON s.dept_id=d.dept_id

    '''
    if(sname!="" or student_id!="" ):
            sql+='where '
            if(sname!=""):
                sql+=f"sname like '%{sname}%' and "
            if(student_id!=""):
                sql+=f"s.student_id like '%{student_id}%' and "
            sql=sql[:-4]
    sql+='group by s.student_id'

    headers=['sname','student_id','grade','sex','native_place','mobile_phone','dept_id','total_credit']
    print(sql)
    cur.execute(sql)
    results=cur.fetchall()
    results=list(results)
    for i in range(len(results)):
        results[i]=list(results[i])
        sql=f'''
            select SUM(c.credit) from course_selection cs
            join course c on cs.course_id=c.course_id
            where student_id={results[i][1]} and cs.total_score>=60
            '''
        cur.execute(sql)
        total_credit=cur.fetchone()[0]
        results[i].append(total_credit)
    print(results)
    res=[]
    for item in results:
         itemdic={}
         for i in range(len(headers)):
             itemdic[headers[i]]=item[i]
         res.append(itemdic)

    resp={"data":res}
    print(resp)
    connection.commit()
    cur.close()
    response=JsonResponse(resp)
    return response

def admin_student_delete(request):
    student_id=request.GET['student_id']
    cur=connection.cursor()
    sql='''delete from student where student_id=%s;
    '''
    cur.execute(sql,(student_id,))
    connection.commit()
    cur.close()
    response=JsonResponse({"data":1})
    return response

def admin_student_add(request):
    sname=request.GET['sname']
    student_id=request.GET['student_id']
    grade=request.GET['grade']
    sex=request.GET['sex']
    native_place=request.GET['native_place']
    mobile_phone=request.GET['mobile_phone']
    dept_name=request.GET['dept_name']
    cur=connection.cursor()
    sql = '''
        INSERT INTO student (sname, student_id, grade, sex, native_place, mobile_phone, dept_id)
        SELECT %s, %s, %s, %s, %s, %s, department.dept_id
        FROM department
        WHERE department.dept_name = %s;
    '''
    cur.execute(sql,(sname,student_id,grade,sex,native_place,mobile_phone,dept_name))
    connection.commit()
    cur.close()
    response=JsonResponse({"data":1})
    return response

def admin_student_update(request):
    sname=request.GET['sname']
    student_id=request.GET['student_id']  
    grade=request.GET['grade']
    sex=request.GET['sex']
    native_place=request.GET['native_place']
    mobile_phone=request.GET['mobile_phone']
    dept_name=request.GET['dept_name']
    cur=connection.cursor()
    sql=f'''
        UPDATE student
        SET
            sname = '{sname}',
            grade = '{grade}',
            sex = '{sex}',
            native_place = '{native_place}',
            mobile_phone = '{mobile_phone}',
            dept_id = (
                SELECT dept_id
                FROM department
                WHERE dept_name = '{dept_name}'
            )
        WHERE
            student_id = '{student_id}';
    '''
    cur.execute(sql)
    connection.commit()
    cur.close()
    response=JsonResponse({"data":1})
    return response


def admin_teacher(request):
    staff_id=request.GET['staff_id']
    tname=request.GET['tname']
    cur=connection.cursor()
    sql='''
    SELECT t.tname, t.staff_id, t.sex, t.professional_ranks, d.dept_name FROM teacher t
    JOIN department d ON t.dept_id=d.dept_id
    '''
    if(tname!="" or staff_id!="" ):
            sql+='where '
            if(tname!=""):
                sql+=f"tname like '%{tname}%' and "
            if(staff_id!=""):
                sql+=f"staff_id like '%{staff_id}%' and "
            sql=sql[:-4]

    
    headers=['tname','staff_id','sex','professional_ranks','dept_id']
    print(sql)
    cur.execute(sql)
    results=cur.fetchall()
    res=[]
    for item in results:
         itemdic={}
         for i in range(len(headers)):
             itemdic[headers[i]]=item[i]
         res.append(itemdic)

    resp={"data":res}
    print(resp)
    connection.commit()
    cur.close()
    response=JsonResponse(resp)
    return response

def admin_teacher_delete(request):
    staff_id=request.GET['staff_id']
    cur=connection.cursor()
    sql='''delete from teacher where staff_id=%s;
    '''
    cur.execute(sql,(staff_id,))
    connection.commit()
    cur.close()
    response=JsonResponse({"data":1})
    return response

def admin_teacher_add(request):
    print("adding_teacher")
    tname=request.GET['tname']
    staff_id=request.GET['staff_id']
    sex=request.GET['sex']
    professional_ranks=request.GET['professional_ranks']
    dept_name=request.GET['dept_name']
    cur=connection.cursor()
    sql = '''
        INSERT INTO teacher (tname, staff_id, sex, professional_ranks, dept_id)
        SELECT %s, %s, %s, %s, department.dept_id
        FROM department
        WHERE department.dept_name = %s;
    '''
    cur.execute(sql,(tname,staff_id,sex,professional_ranks,dept_name))
    connection.commit()
    cur.close()
    response=JsonResponse({"data":1})
    return response

def admin_teacher_update(request):
    tname=request.GET['tname']
    staff_id=request.GET['staff_id']  
    sex=request.GET['sex']
    professional_ranks=request.GET['professional_ranks']
    dept_name=request.GET['dept_name']
    cur=connection.cursor()
    sql=f'''
        UPDATE teacher
        SET
            tname = '{tname}',
            sex = '{sex}',
            professional_ranks = '{professional_ranks}',
            dept_id = (
                SELECT dept_id
                FROM department
                WHERE dept_name = '{dept_name}'
            )
        WHERE
            staff_id = '{staff_id}';
    '''
    cur.execute(sql)
    connection.commit()
    cur.close()
    response=JsonResponse({"data":1})
    return response
def admin_course(request):
    course_id=request.GET['course_id']
    course_name=request.GET['course_name']
    cur=connection.cursor()
    sql='''
    SELECT c.course_id, c.course_name, c.credit, c.credit_hours, c.dept_id,d.dept_name FROM course c
    JOIN department d ON c.dept_id=d.dept_id
    '''
    if(course_id!="" or course_name!="" ):
        sql+='where '
        if(course_id!=""):
            sql+=f"course_id like '%{course_id}%' and "
        if(course_name!=""):
            sql+=f"course_name like '%{course_name}%' and "
        sql=sql[:-4]
    headers=['course_id','course_name','credit','credit_hours','dept_id','dept_name']
    print(sql)
    cur.execute(sql)
    results=cur.fetchall()
    res=[]
    for item in results:
         itemdic={}
         for i in range(len(headers)):
             itemdic[headers[i]]=item[i]
         res.append(itemdic)

    resp={"data":res}
    print(resp)
    connection.commit()
    cur.close()
    response=JsonResponse(resp)
    return response


def admin_course_delete(request):
    course_id=request.GET['course_id']
    cur=connection.cursor()
    sql='''delete from course where course_id=%s;
    '''
    cur.execute(sql,(course_id,))
    connection.commit()
    cur.close()
    response=JsonResponse({"data":1})
    return response

def admin_course_add(request):
    course_id=request.GET['course_id']
    course_name=request.GET['course_name']
    credit=request.GET['credit']
    credit_hours=request.GET['credit_hours']
    dept_id=request.GET['dept_id']
    cur=connection.cursor()
    sql = f'''
        INSERT INTO course (course_id, course_name, credit, credit_hours, dept_id)
        VALUES('{course_id}','{course_name}',{credit},{credit_hours},'{dept_id}')
    '''
    cur.execute(sql)
    connection.commit()
    cur.close()
    response=JsonResponse({"data":1})
    return response

def admin_course_update(request):
    course_id=request.GET['course_id']
    course_name=request.GET['course_name']
    credit=request.GET['credit']
    credit_hours=request.GET['credit_hours']
    dept_id=request.GET['dept_id']
    cur=connection.cursor()
    sql=f'''
        UPDATE course
        SET
            course_name = '{course_name}',
            credit = {credit},
            credit_hours = {credit_hours},
            dept_id = '{dept_id}'
        WHERE 
            course_id = '{course_id}'
    '''
    print(sql)
    cur.execute(sql)
    connection.commit()
    cur.close()
    response=JsonResponse({"data":1})
    return response


def admin_course_selection(request):
    course_id=request.GET['course_id']
    student_id=request.GET['student_id']
    staff_id=request.GET['staff_id']
    cur=connection.cursor()
    sql=f'''
    select 
        cs.course_id,
        co.course_name,
        cs.staff_id,
        t.tname AS teacher_name,
        cs.student_id,
        s.sname AS student_name,
        cs.semester,
        cs.normal_score,
        cs.test_score,
        cs.total_score,
        cs.gpa
    from course_selection cs
    join student s on cs.student_id=s.student_id
    join teacher t on cs.staff_id=t.staff_id
    join course co on cs.course_id=co.course_id
    '''
    if(course_id!="" or student_id!="" or staff_id!=""):
        sql+='where '
        if(course_id!=""):
            sql+=f"cs.course_id like '%{course_id}%' and "
        if(student_id!=""):
            sql+=f"cs.student_id like '%{student_id}%' and "
        if(staff_id!=""):
            sql+=f"cs.staff_id like '%{staff_id}%' and "
        sql=sql[:-4]
    print(sql)
    cur.execute(sql)

    data=cur.fetchall()
    headers=['course_id','course_name','staff_id','teacher_name','student_id','student_name','semester','normal_score','test_score','total_score','gpa']
    res=[]
    for item in data:
        tmpdic={}
        for i in range(len(headers)):
            tmpdic[headers[i]]=item[i]
        res.append(tmpdic)
    print(res)
    return JsonResponse({'code':200,'msg':"查询成功",'data':res})

def admin_course_selection_add(request):
    course_id = request.GET['course_id']
    student_id = int(request.GET['student_id'])
    semester = request.GET['semester']
    staff_id = request.GET['staff_id']
    
    cur = connection.cursor()
    
    # 使用参数化查询插入数据
    sql = '''
    INSERT INTO course_selection (student_id, semester, course_id, staff_id, normal_score, test_score, total_score, gpa)
    VALUES (%s, %s, %s, %s, 0, 0, 0, 0)
    '''
    
    # 执行SQL语句
    cur.execute(sql, (student_id, semester, course_id, staff_id))
    
    # 提交事务并关闭游标
    connection.commit()
    cur.close()
    
    # 返回JSON响应
    response = JsonResponse({"data": 1})
    return response


def admin_course_selection_delete(request):
    course_id=request.GET['course_id']
    student_id=request.GET['student_id']
    semester=request.GET['semester']
    staff_id=request.GET['staff_id']
    cur=connection.cursor()
    sql=f'''
    delete from course_selection
    where student_id={student_id} and semester='{semester}' and course_id='{course_id}' and staff_id={staff_id}
    '''
    print(sql)
    cur.execute(sql)
    connection.commit()
    return JsonResponse({'code':200,'msg':"删除成功"})

def admin_course_selection_update(request):
    course_id=request.GET['course_id']
    student_id=request.GET['student_id']
    semester=request.GET['semester']
    staff_id=request.GET['staff_id']
    normal_score=request.GET['normal_score']
    test_score=request.GET['test_score']
    total_score=request.GET['total_score']
    gpa=request.GET['gpa']
    cur=connection.cursor()
    sql=f'''
    update course_selection
    set 
        normal_score={normal_score},
        test_score={test_score},
        total_score={total_score},
        gpa={gpa}
    where
        course_id='{course_id}' and
        student_id={student_id} and
        semester='{semester}' and
        staff_id='{staff_id}'
    '''
    print(sql)
    cur.execute(sql)
    connection.commit()
    return JsonResponse({'code':200,'msg':"更新成功"})

def admin_dept(request):
    dept_id=request.GET['dept_id']
    dept_name=request.GET['dept_name']
    cur=connection.cursor()
    sql='''
    SELECT dept_id, dept_name, address, phone_code FROM department
    '''
    if(dept_id!="" or dept_name!="" ):
        sql+='where '
        if(dept_id!=""):
            sql+=f"dept_id like '%{dept_id}%' and "
        if(dept_name!=""):
            sql+=f"dept_name like '%{dept_name}%' and "
        sql=sql[:-4]
    headers=['dept_id','dept_name','address','phone_code']
    print(sql)
    cur.execute(sql)
    results=cur.fetchall()
    res=[]
    for item in results:
         itemdic={}
         for i in range(len(headers)):
             itemdic[headers[i]]=item[i]
         res.append(itemdic)

    resp={"data":res}
    print(resp)
    connection.commit()
    cur.close()
    response=JsonResponse(resp)
    return response

def admin_dept_add(request):
    dept_id=request.GET['dept_id']
    dept_name=request.GET['dept_name']
    address=request.GET['address']
    phone_code=request.GET['phone_code']
    cur=connection.cursor()
    sql = f'''
        INSERT INTO department (dept_id, dept_name, address, phone_code)
        VALUES('{dept_id}','{dept_name}','{address}','{phone_code}')
    '''
    cur.execute(sql)
    connection.commit()
    cur.close()
    response=JsonResponse({"data":1})
    return response

def admin_dept_update(request):
    dept_id=request.GET['dept_id']
    dept_name=request.GET['dept_name']
    address=request.GET['address']
    phone_code=request.GET['phone_code']
    cur=connection.cursor()
    sql=f'''
        UPDATE department
        SET
            dept_name = '{dept_name}',
            address = '{address}',
            phone_code = '{phone_code}'
        WHERE 
            dept_id = '{dept_id}'
    '''
    print(sql)
    cur.execute(sql)
    connection.commit()
    cur.close()
    response=JsonResponse({"data":1})
    return response

def admin_classs(request):
    course_id=request.GET['course_id']
    staff_id=request.GET['staff_id']
    cur=connection.cursor()
    sql='''
    SELECT cl.semester, cl.course_id, co.course_name, cl.class_time, cl.staff_id, t.tname FROM class cl
    JOIN course co ON cl.course_id=co.course_id
    JOIN teacher t ON cl.staff_id=t.staff_id
    '''
    if(course_id!="" or staff_id!="" ):
        sql+='where '
        if(course_id!=""):
            sql+=f"cl.course_id like '%{course_id}%' and "
        if(staff_id!=""):
            sql+=f"cl.staff_id like '%{staff_id}%' and "
        sql=sql[:-4]
    headers=['semester','course_id','course_name','class_time','staff_id','tname']
    print(sql)
    cur.execute(sql)
    results=cur.fetchall()
    res=[]
    for item in results:
         itemdic={}
         for i in range(len(headers)):
             itemdic[headers[i]]=item[i]
         res.append(itemdic)

    resp={"data":res}
    print(resp)
    connection.commit()
    cur.close()
    response=JsonResponse(resp)
    return response

def admin_classs_add(request):
    semester=request.GET['semester']
    course_id=request.GET['course_id']
    class_time=request.GET['class_time']
    staff_id=request.GET['staff_id']
    cur=connection.cursor()
    sql = f'''
        INSERT INTO class (semester, course_id, staff_id, class_time)
        VALUES('{semester}','{course_id}','{staff_id}','{class_time}')
    '''
    cur.execute(sql)
    connection.commit()
    cur.close()
    response=JsonResponse({"data":1})
    return response

def admin_classs_update(request):
    semester=request.GET['semester']
    course_id=request.GET['course_id']
    class_time=request.GET['class_time']
    staff_id=request.GET['staff_id']
    cur=connection.cursor()
    sql=f'''
        UPDATE class
        SET
            semester = '{semester}',
            class_time = '{class_time}'
        WHERE 
            course_id = '{course_id}' and staff_id = '{staff_id}'
    '''
    print(sql)
    cur.execute(sql)
    connection.commit()
    cur.close()
    response=JsonResponse({"data":1})
    return response

def admin_test(request):
    
    sql=f'''
        select * from student
    '''
    print(sql)
    cur=connection.cursor()
    cur.execute(sql)
    rows=cur.fetchall()
    print(rows)
    cur.close()
    response=JsonResponse({"data":1})